# coding=UTF-8
# 导入模块
from openpyxl import load_workbook
import datetime
import re
# 导入一个Workbook

#excel名字
excelName='erroInput.xlsx'

#sheet名字
sheetName='heyuan'

#写入的行
wRow=5
#读取对比的行
rRow=4

#注册 及上市时间
signCol=3
inputCol=2




wb = load_workbook(filename = excelName)
sheet=wb.get_sheet_by_name(sheetName)
row_max=sheet.max_row


for r in range(2,row_max):
    if sheet.cell(row=r, column=signCol).value ==None:


        pass
    else:
        s1=sheet.cell(row=r, column=inputCol).value
        s2=sheet.cell(row=r, column=signCol).value
        inputTime=None
        signTime=s2
        if isinstance(s1,str):
            inputTime= datetime.datetime.strptime(s1[0:19], '%Y-%m-%d %H:%M:%S')
        else:
            inputTime=s1

       # print(inputTime,"     ",inputTime+datetime.timedelta(days=3))

        #购买日期比上市 日期还要早则处理
        if signTime<inputTime:
            lic=sheet.cell(row=r, column=1).value

            #去空值
            if lic==None:
                continue
            # print(lic)
            licNum=re.findall(r'([0-9]+)',lic)
            if len(licNum)>0:
                year=licNum[0]
                # print(year)
                if len(year)>3:
                    # print(year[0:4])
                    licTime = datetime.datetime.strptime(year[0:4]+'-03-02 17:41:20', '%Y-%m-%d %H:%M:%S')
                    delta=signTime-licTime
                    newLicTime=licTime+datetime.timedelta(days=delta.days/2)
                    print(newLicTime,'   ',inputTime,'    ',signTime)
                    sheet['B' + str(r)].value = newLicTime



        #print(s1,"   ",sheet.cell(row=r, column=signCol).value)



# wb.save('b_gy.xlsx')
# s='2018-03-18 02:55:23.000002'
# print(s[0:19])
#
#
# d1 = datetime.datetime.strptime(s[0:19], '%Y-%m-%d %H:%M:%S')
# d2 = datetime.datetime.strptime('2015-03-02 17:41:20', '%Y-%m-%d %H:%M:%S')
# delta = d2 - d1


# if d2>d1:
#     print('dayu')
# else:
#     print('xiaoyu')
# print(delta)
wb.save('out.xlsx')